
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 08/20/2010 18:58:56
-- Generated from EDMX file: C:\Users\hirosho\Documents\Visual Studio 10\Projects\GoVA\ETRoboConTrackingSystemServer\ETRoboConTrackingSystemServer\GoVADataModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [GoVA];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------


-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'ContestSet'
CREATE TABLE [dbo].[ContestSet] (
    [ContestId] int IDENTITY(1,1) NOT NULL,
    [Year] int  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Memo] nvarchar(max)  NULL
);
GO

-- Creating table 'GameSet'
CREATE TABLE [dbo].[GameSet] (
    [GameId] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Memo] nvarchar(max)  NULL,
    [Contest_ContestId] int  NOT NULL,
    [Area_AreaId] int  NOT NULL,
    [GameSpec_GameSpecId] int  NOT NULL
);
GO

-- Creating table 'AreaSet'
CREATE TABLE [dbo].[AreaSet] (
    [AreaId] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Contest_ContestId] int  NOT NULL
);
GO

-- Creating table 'GameSpecSet'
CREATE TABLE [dbo].[GameSpecSet] (
    [GameSpecId] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Memo] nvarchar(max)  NULL
);
GO

-- Creating table 'TeamSet'
CREATE TABLE [dbo].[TeamSet] (
    [TeamId] int IDENTITY(1,1) NOT NULL,
    [Id] uniqueidentifier  NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Yomi] nvarchar(max)  NULL,
    [Memo] nvarchar(max)  NULL,
    [Organization] nvarchar(max)  NULL,
    [Category] nvarchar(max)  NOT NULL,
    [RegId] bigint  NOT NULL,
    [Area_AreaId] int  NOT NULL
);
GO

-- Creating table 'EntryTeamSet'
CREATE TABLE [dbo].[EntryTeamSet] (
    [EntryTeamId] int IDENTITY(1,1) NOT NULL,
    [Bib] int  NOT NULL,
    [Exibition] bit  NOT NULL,
    [Memo] nvarchar(max)  NULL,
    [Rank] int  NULL,
    [RaceTotalPoint] int  NULL,
    [ModelTotalPoint] int  NULL,
    [Team_TeamId] int  NOT NULL,
    [Game_GameId] int  NOT NULL
);
GO

-- Creating table 'RoundSet'
CREATE TABLE [dbo].[RoundSet] (
    [RoundId] int IDENTITY(1,1) NOT NULL,
    [RoundNo] int  NOT NULL,
    [Game_GameId] int  NOT NULL
);
GO

-- Creating table 'TurnSet'
CREATE TABLE [dbo].[TurnSet] (
    [TurnId] int IDENTITY(1,1) NOT NULL,
    [TurnNo] int  NOT NULL,
    [Round_RoundId] int  NOT NULL,
    [InCourseEntryTeam_EntryTeamId] int  NULL,
    [OutCourseEntryTeam_EntryTeamId] int  NULL
);
GO

-- Creating table 'GameCourseSpecSet'
CREATE TABLE [dbo].[GameCourseSpecSet] (
    [GameCourseSpecId] int IDENTITY(1,1) NOT NULL,
    [CourseName] nvarchar(max)  NOT NULL,
    [GameSpec_GameSpecId] int  NOT NULL
);
GO

-- Creating table 'RaceSet'
CREATE TABLE [dbo].[RaceSet] (
    [RaceId] int IDENTITY(1,1) NOT NULL,
    [TurnOrder] int  NOT NULL,
    [Turn_TurnId] int  NOT NULL
);
GO

-- Creating table 'TurnRunnerSet'
CREATE TABLE [dbo].[TurnRunnerSet] (
    [TurnRunnerId] int IDENTITY(1,1) NOT NULL,
    [RunTime] int  NULL,
    [ResultPoint] int  NULL,
    [Status] nvarchar(max)  NULL,
    [EntryTeam_EntryTeamId] int  NOT NULL,
    [Turn_TurnId] int  NOT NULL,
    [Race_RaceId] int  NULL,
    [GameCourseSpec_GameCourseSpecId] int  NOT NULL
);
GO

-- Creating table 'BonusSpecSet'
CREATE TABLE [dbo].[BonusSpecSet] (
    [BonusSpecId] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [ShortName] nvarchar(max)  NOT NULL,
    [Specification] nvarchar(max)  NULL,
    [Point] int  NOT NULL,
    [ClearOrder] int  NOT NULL,
    [Extension] nvarchar(max)  NULL,
    [GameCourseSpec_GameCourseSpecId] int  NOT NULL
);
GO

-- Creating table 'RunnerPointSet'
CREATE TABLE [dbo].[RunnerPointSet] (
    [RunnerPointId] int IDENTITY(1,1) NOT NULL,
    [Available] bit  NOT NULL,
    [Acquired] bit  NOT NULL,
    [TurnRunner_TurnRunnerId] int  NOT NULL,
    [BonusSpec_BonusSpecId] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ContestId] in table 'ContestSet'
ALTER TABLE [dbo].[ContestSet]
ADD CONSTRAINT [PK_ContestSet]
    PRIMARY KEY CLUSTERED ([ContestId] ASC);
GO

-- Creating primary key on [GameId] in table 'GameSet'
ALTER TABLE [dbo].[GameSet]
ADD CONSTRAINT [PK_GameSet]
    PRIMARY KEY CLUSTERED ([GameId] ASC);
GO

-- Creating primary key on [AreaId] in table 'AreaSet'
ALTER TABLE [dbo].[AreaSet]
ADD CONSTRAINT [PK_AreaSet]
    PRIMARY KEY CLUSTERED ([AreaId] ASC);
GO

-- Creating primary key on [GameSpecId] in table 'GameSpecSet'
ALTER TABLE [dbo].[GameSpecSet]
ADD CONSTRAINT [PK_GameSpecSet]
    PRIMARY KEY CLUSTERED ([GameSpecId] ASC);
GO

-- Creating primary key on [TeamId] in table 'TeamSet'
ALTER TABLE [dbo].[TeamSet]
ADD CONSTRAINT [PK_TeamSet]
    PRIMARY KEY CLUSTERED ([TeamId] ASC);
GO

-- Creating primary key on [EntryTeamId] in table 'EntryTeamSet'
ALTER TABLE [dbo].[EntryTeamSet]
ADD CONSTRAINT [PK_EntryTeamSet]
    PRIMARY KEY CLUSTERED ([EntryTeamId] ASC);
GO

-- Creating primary key on [RoundId] in table 'RoundSet'
ALTER TABLE [dbo].[RoundSet]
ADD CONSTRAINT [PK_RoundSet]
    PRIMARY KEY CLUSTERED ([RoundId] ASC);
GO

-- Creating primary key on [TurnId] in table 'TurnSet'
ALTER TABLE [dbo].[TurnSet]
ADD CONSTRAINT [PK_TurnSet]
    PRIMARY KEY CLUSTERED ([TurnId] ASC);
GO

-- Creating primary key on [GameCourseSpecId] in table 'GameCourseSpecSet'
ALTER TABLE [dbo].[GameCourseSpecSet]
ADD CONSTRAINT [PK_GameCourseSpecSet]
    PRIMARY KEY CLUSTERED ([GameCourseSpecId] ASC);
GO

-- Creating primary key on [RaceId] in table 'RaceSet'
ALTER TABLE [dbo].[RaceSet]
ADD CONSTRAINT [PK_RaceSet]
    PRIMARY KEY CLUSTERED ([RaceId] ASC);
GO

-- Creating primary key on [TurnRunnerId] in table 'TurnRunnerSet'
ALTER TABLE [dbo].[TurnRunnerSet]
ADD CONSTRAINT [PK_TurnRunnerSet]
    PRIMARY KEY CLUSTERED ([TurnRunnerId] ASC);
GO

-- Creating primary key on [BonusSpecId] in table 'BonusSpecSet'
ALTER TABLE [dbo].[BonusSpecSet]
ADD CONSTRAINT [PK_BonusSpecSet]
    PRIMARY KEY CLUSTERED ([BonusSpecId] ASC);
GO

-- Creating primary key on [RunnerPointId] in table 'RunnerPointSet'
ALTER TABLE [dbo].[RunnerPointSet]
ADD CONSTRAINT [PK_RunnerPointSet]
    PRIMARY KEY CLUSTERED ([RunnerPointId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Contest_ContestId] in table 'AreaSet'
ALTER TABLE [dbo].[AreaSet]
ADD CONSTRAINT [FK_ContestArea]
    FOREIGN KEY ([Contest_ContestId])
    REFERENCES [dbo].[ContestSet]
        ([ContestId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ContestArea'
CREATE INDEX [IX_FK_ContestArea]
ON [dbo].[AreaSet]
    ([Contest_ContestId]);
GO

-- Creating foreign key on [Contest_ContestId] in table 'GameSet'
ALTER TABLE [dbo].[GameSet]
ADD CONSTRAINT [FK_ContestGame]
    FOREIGN KEY ([Contest_ContestId])
    REFERENCES [dbo].[ContestSet]
        ([ContestId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ContestGame'
CREATE INDEX [IX_FK_ContestGame]
ON [dbo].[GameSet]
    ([Contest_ContestId]);
GO

-- Creating foreign key on [Area_AreaId] in table 'GameSet'
ALTER TABLE [dbo].[GameSet]
ADD CONSTRAINT [FK_AreaGame]
    FOREIGN KEY ([Area_AreaId])
    REFERENCES [dbo].[AreaSet]
        ([AreaId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AreaGame'
CREATE INDEX [IX_FK_AreaGame]
ON [dbo].[GameSet]
    ([Area_AreaId]);
GO

-- Creating foreign key on [GameSpec_GameSpecId] in table 'GameSet'
ALTER TABLE [dbo].[GameSet]
ADD CONSTRAINT [FK_GameSpecGame]
    FOREIGN KEY ([GameSpec_GameSpecId])
    REFERENCES [dbo].[GameSpecSet]
        ([GameSpecId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GameSpecGame'
CREATE INDEX [IX_FK_GameSpecGame]
ON [dbo].[GameSet]
    ([GameSpec_GameSpecId]);
GO

-- Creating foreign key on [Area_AreaId] in table 'TeamSet'
ALTER TABLE [dbo].[TeamSet]
ADD CONSTRAINT [FK_AreaTeam]
    FOREIGN KEY ([Area_AreaId])
    REFERENCES [dbo].[AreaSet]
        ([AreaId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AreaTeam'
CREATE INDEX [IX_FK_AreaTeam]
ON [dbo].[TeamSet]
    ([Area_AreaId]);
GO

-- Creating foreign key on [Team_TeamId] in table 'EntryTeamSet'
ALTER TABLE [dbo].[EntryTeamSet]
ADD CONSTRAINT [FK_TeamEntryTeam]
    FOREIGN KEY ([Team_TeamId])
    REFERENCES [dbo].[TeamSet]
        ([TeamId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeamEntryTeam'
CREATE INDEX [IX_FK_TeamEntryTeam]
ON [dbo].[EntryTeamSet]
    ([Team_TeamId]);
GO

-- Creating foreign key on [Game_GameId] in table 'EntryTeamSet'
ALTER TABLE [dbo].[EntryTeamSet]
ADD CONSTRAINT [FK_GameEntryTeam]
    FOREIGN KEY ([Game_GameId])
    REFERENCES [dbo].[GameSet]
        ([GameId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GameEntryTeam'
CREATE INDEX [IX_FK_GameEntryTeam]
ON [dbo].[EntryTeamSet]
    ([Game_GameId]);
GO

-- Creating foreign key on [Game_GameId] in table 'RoundSet'
ALTER TABLE [dbo].[RoundSet]
ADD CONSTRAINT [FK_GameRound]
    FOREIGN KEY ([Game_GameId])
    REFERENCES [dbo].[GameSet]
        ([GameId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GameRound'
CREATE INDEX [IX_FK_GameRound]
ON [dbo].[RoundSet]
    ([Game_GameId]);
GO

-- Creating foreign key on [Round_RoundId] in table 'TurnSet'
ALTER TABLE [dbo].[TurnSet]
ADD CONSTRAINT [FK_RoundTurn]
    FOREIGN KEY ([Round_RoundId])
    REFERENCES [dbo].[RoundSet]
        ([RoundId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RoundTurn'
CREATE INDEX [IX_FK_RoundTurn]
ON [dbo].[TurnSet]
    ([Round_RoundId]);
GO

-- Creating foreign key on [InCourseEntryTeam_EntryTeamId] in table 'TurnSet'
ALTER TABLE [dbo].[TurnSet]
ADD CONSTRAINT [FK_TurnEntryTeam]
    FOREIGN KEY ([InCourseEntryTeam_EntryTeamId])
    REFERENCES [dbo].[EntryTeamSet]
        ([EntryTeamId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TurnEntryTeam'
CREATE INDEX [IX_FK_TurnEntryTeam]
ON [dbo].[TurnSet]
    ([InCourseEntryTeam_EntryTeamId]);
GO

-- Creating foreign key on [OutCourseEntryTeam_EntryTeamId] in table 'TurnSet'
ALTER TABLE [dbo].[TurnSet]
ADD CONSTRAINT [FK_TurnEntryTeam1]
    FOREIGN KEY ([OutCourseEntryTeam_EntryTeamId])
    REFERENCES [dbo].[EntryTeamSet]
        ([EntryTeamId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TurnEntryTeam1'
CREATE INDEX [IX_FK_TurnEntryTeam1]
ON [dbo].[TurnSet]
    ([OutCourseEntryTeam_EntryTeamId]);
GO

-- Creating foreign key on [GameSpec_GameSpecId] in table 'GameCourseSpecSet'
ALTER TABLE [dbo].[GameCourseSpecSet]
ADD CONSTRAINT [FK_GameSpecGameCourseSpec]
    FOREIGN KEY ([GameSpec_GameSpecId])
    REFERENCES [dbo].[GameSpecSet]
        ([GameSpecId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GameSpecGameCourseSpec'
CREATE INDEX [IX_FK_GameSpecGameCourseSpec]
ON [dbo].[GameCourseSpecSet]
    ([GameSpec_GameSpecId]);
GO

-- Creating foreign key on [Turn_TurnId] in table 'RaceSet'
ALTER TABLE [dbo].[RaceSet]
ADD CONSTRAINT [FK_TurnRace]
    FOREIGN KEY ([Turn_TurnId])
    REFERENCES [dbo].[TurnSet]
        ([TurnId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TurnRace'
CREATE INDEX [IX_FK_TurnRace]
ON [dbo].[RaceSet]
    ([Turn_TurnId]);
GO

-- Creating foreign key on [EntryTeam_EntryTeamId] in table 'TurnRunnerSet'
ALTER TABLE [dbo].[TurnRunnerSet]
ADD CONSTRAINT [FK_EntryTeamTurnRunner]
    FOREIGN KEY ([EntryTeam_EntryTeamId])
    REFERENCES [dbo].[EntryTeamSet]
        ([EntryTeamId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EntryTeamTurnRunner'
CREATE INDEX [IX_FK_EntryTeamTurnRunner]
ON [dbo].[TurnRunnerSet]
    ([EntryTeam_EntryTeamId]);
GO

-- Creating foreign key on [Turn_TurnId] in table 'TurnRunnerSet'
ALTER TABLE [dbo].[TurnRunnerSet]
ADD CONSTRAINT [FK_TurnTurnRunner]
    FOREIGN KEY ([Turn_TurnId])
    REFERENCES [dbo].[TurnSet]
        ([TurnId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TurnTurnRunner'
CREATE INDEX [IX_FK_TurnTurnRunner]
ON [dbo].[TurnRunnerSet]
    ([Turn_TurnId]);
GO

-- Creating foreign key on [Race_RaceId] in table 'TurnRunnerSet'
ALTER TABLE [dbo].[TurnRunnerSet]
ADD CONSTRAINT [FK_RaceTurnRunner]
    FOREIGN KEY ([Race_RaceId])
    REFERENCES [dbo].[RaceSet]
        ([RaceId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RaceTurnRunner'
CREATE INDEX [IX_FK_RaceTurnRunner]
ON [dbo].[TurnRunnerSet]
    ([Race_RaceId]);
GO

-- Creating foreign key on [GameCourseSpec_GameCourseSpecId] in table 'TurnRunnerSet'
ALTER TABLE [dbo].[TurnRunnerSet]
ADD CONSTRAINT [FK_GameCourseSpecTurnRunner]
    FOREIGN KEY ([GameCourseSpec_GameCourseSpecId])
    REFERENCES [dbo].[GameCourseSpecSet]
        ([GameCourseSpecId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GameCourseSpecTurnRunner'
CREATE INDEX [IX_FK_GameCourseSpecTurnRunner]
ON [dbo].[TurnRunnerSet]
    ([GameCourseSpec_GameCourseSpecId]);
GO

-- Creating foreign key on [GameCourseSpec_GameCourseSpecId] in table 'BonusSpecSet'
ALTER TABLE [dbo].[BonusSpecSet]
ADD CONSTRAINT [FK_GameCourseSpecBonusSpec]
    FOREIGN KEY ([GameCourseSpec_GameCourseSpecId])
    REFERENCES [dbo].[GameCourseSpecSet]
        ([GameCourseSpecId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GameCourseSpecBonusSpec'
CREATE INDEX [IX_FK_GameCourseSpecBonusSpec]
ON [dbo].[BonusSpecSet]
    ([GameCourseSpec_GameCourseSpecId]);
GO

-- Creating foreign key on [TurnRunner_TurnRunnerId] in table 'RunnerPointSet'
ALTER TABLE [dbo].[RunnerPointSet]
ADD CONSTRAINT [FK_TurnRunnerRunnerPoint]
    FOREIGN KEY ([TurnRunner_TurnRunnerId])
    REFERENCES [dbo].[TurnRunnerSet]
        ([TurnRunnerId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TurnRunnerRunnerPoint'
CREATE INDEX [IX_FK_TurnRunnerRunnerPoint]
ON [dbo].[RunnerPointSet]
    ([TurnRunner_TurnRunnerId]);
GO

-- Creating foreign key on [BonusSpec_BonusSpecId] in table 'RunnerPointSet'
ALTER TABLE [dbo].[RunnerPointSet]
ADD CONSTRAINT [FK_BonusSpecRunnerPoint]
    FOREIGN KEY ([BonusSpec_BonusSpecId])
    REFERENCES [dbo].[BonusSpecSet]
        ([BonusSpecId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BonusSpecRunnerPoint'
CREATE INDEX [IX_FK_BonusSpecRunnerPoint]
ON [dbo].[RunnerPointSet]
    ([BonusSpec_BonusSpecId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------